﻿ 
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace XW.Utils.Tool
{
   public class ImportHelper
    {


        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportFromExcel(string excelFilePath, string sheetName, int headerRowIndex,int val)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                bool isCompatible = GetIsCompatible(excelFilePath);
                return ImportFromExcel(stream, sheetName, headerRowIndex, isCompatible,val);
            }
        }

        /// <summary>
        /// 判断是否为兼容模式
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private static bool GetIsCompatible(string filePath)
        {
            return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);
        }

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <param name="isCompatible">是否为兼容模式</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible,int val)
        {
            IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
            ISheet sheet = null;
            int sheetIndex = -1;
            if (int.TryParse(sheetName, out sheetIndex))
            {
                sheet = workbook.GetSheetAt(sheetIndex);
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }

            DataTable table = GetDataTableFromSheet(sheet, headerRowIndex,val);

            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }
        /// <summary>
        /// 导入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelFilePath"></param>
        /// <param name="sheetName"></param>
        /// <param name="headerRowIndex"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static List<T> Import<T>(string excelFilePath, string sheetName="", int headerRowIndex=0, List<ImportColumn> columns = null) where T : class, new()
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                bool isCompatible = GetIsCompatible(excelFilePath);
                return Import<T>(stream, sheetName, headerRowIndex, isCompatible, columns);
            }
        }
        ///// <summary>
        ///// 获取导入的形状
        ///// </summary>
        ///// <param name="excelFilePath">The excel file path.</param>
        ///// <returns></returns>
        //public static List<ExcelCustomGeometryModel> GetImportCustomGeometry(string excelFilePath) {

        //    List<ExcelCustomGeometryModel> result = new List<ExcelCustomGeometryModel>();
        //    using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
        //    {  
        //        var document = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(stream, false);
        //        var workbook = document.WorkbookPart.Workbook;
        //        var sheet = workbook.Descendants<DocumentFormat.OpenXml.Spreadsheet.Sheet>().FirstOrDefault();
        //        var worksheetPart = document.WorkbookPart.GetPartById(sheet.Id);
        //        var drawing = workbook.Descendants<DocumentFormat.OpenXml.Spreadsheet.Drawing>().FirstOrDefault();
        //        var childs = ((DocumentFormat.OpenXml.Packaging.WorksheetPart)worksheetPart).DrawingsPart.WorksheetDrawing.ChildElements.OfType<DocumentFormat.OpenXml.Drawing.Spreadsheet.TwoCellAnchor>();
        //        foreach (var d in childs)
        //        {
        //            var item = TwoCellAnchor2Model(d);
        //            if (item != null)
        //            {
        //                result.Add(item);
        //            } 
        //        }
        //    }
        //    return result;
        //}
        ///// <summary>
        ///// TwoCellAnchor 转换为 ExcelCustomGeometryModel.
        ///// </summary>
        ///// <param name="cellAnchor">The cell anchor.</param>
        ///// <returns></returns>
        //public static ExcelCustomGeometryModel TwoCellAnchor2Model(DocumentFormat.OpenXml.Drawing.Spreadsheet.TwoCellAnchor cellAnchor) {

        //    var childShape = cellAnchor.ChildElements.OfType<DocumentFormat.OpenXml.Drawing.Spreadsheet.Shape>().FirstOrDefault();
        //    var shapeContent = childShape?.ChildElements.OfType<DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties>().FirstOrDefault();
        //    // 图形
        //    var customGenmetry = shapeContent?.ChildElements.OfType<DocumentFormat.OpenXml.Drawing.CustomGeometry>().FirstOrDefault();
        //    var ctPath = customGenmetry?.Descendants<DocumentFormat.OpenXml.Drawing.Path>().FirstOrDefault();
        //    // 如果只画一条线， 则忽略此图形
        //    if (ctPath.ChildElements.Count() == 2
        //        && ctPath.ChildElements.OfType<DocumentFormat.OpenXml.Drawing.LineTo>().Count() == 1
        //        && ctPath.ChildElements.OfType<DocumentFormat.OpenXml.Drawing.MoveTo>().Count() == 1)
        //    {
        //        return null;
        //    }
        //    var item = new ExcelCustomGeometryModel();
        //    item.CustomGeometryXML = cellAnchor.OuterXml;
        //    item.FromRow = Convert.ToInt32(cellAnchor.FromMarker.RowId.InnerText);
        //    item.ToRow = Convert.ToInt32(cellAnchor.ToMarker.RowId.InnerText);
           
        //    // 颜色填充
        //    var solidFill = shapeContent?.ChildElements.OfType<DocumentFormat.OpenXml.Drawing.SolidFill>().FirstOrDefault();
        //    // 填充颜色
        //    var fillColor = solidFill?.ChildElements.OfType<DocumentFormat.OpenXml.Drawing.RgbColorModelHex>().FirstOrDefault()?.Val;
          
        //    var stringPath = new StringBuilder(128);
            
        //    StringBuilder sbSvg = new StringBuilder(128);
        //    sbSvg.AppendFormat("<svg width=\"100%\" height=\"100%\" viewBox=\"0 0 {0} {1}\" xmlns=\"http://www.w3.org/2000/svg\">", ctPath.Width, ctPath.Height);
       
          
        //    foreach (var pathData in ctPath.ChildElements)
        //    {
        //        ConvertToPathString(pathData, stringPath);

        //        stringPath.Append(" ");
        //    }
        //    // 路径
        //    sbSvg.Append("<path d=\"");
        //    sbSvg.Append(stringPath.ToString());

        //    sbSvg.AppendFormat("\" fill=\"#{0}\" stroke=\"black\" stroke-width=\"10\" />",  string.IsNullOrEmpty(fillColor?.Value)? "37CCFF": fillColor?.Value);

        //    sbSvg.Append("</svg>");
        //    item.SVGStr = sbSvg.ToString();
        //    return item;
        //}

        //#region OpenXmlPath转SVG
        //private static void ConvertToPathString(DocumentFormat.OpenXml.OpenXmlElement pathData, StringBuilder stringPath)
        //{
        //    switch (pathData)
        //    {
        //        case DocumentFormat.OpenXml.Drawing.MoveTo moveTo:
        //            {
        //                // 关于定义的 Key 的值请百度参考 svg 规范
        //                var defineKey = "M";
        //                var moveToPoint = moveTo.Point;
        //                if (moveToPoint?.X != null && moveToPoint?.Y != null)
        //                {
        //                    stringPath.Append(defineKey);
        //                    PixelPointToString(moveTo.Point, stringPath);
        //                }

        //                break;
        //            }
        //        case DocumentFormat.OpenXml.Drawing.LineTo lineTo:
        //            {
        //                var defineKey = "L";

        //                var lineToPoint = lineTo.Point;
        //                if (lineToPoint?.X != null && lineToPoint?.Y != null)
        //                {
        //                    stringPath.Append(defineKey);
        //                    PointToString(lineToPoint);
        //                }

        //                break;
        //            }
        //        case DocumentFormat.OpenXml.Drawing.QuadraticBezierCurveTo quadraticBezierCurveTo:
        //            {
        //                var defineKey = "Q";

        //                ConvertPointList(quadraticBezierCurveTo, defineKey, stringPath);
        //                break;
        //            }
        //        case DocumentFormat.OpenXml.Drawing.CubicBezierCurveTo cubicBezierCurveTo:
        //            {
        //                var defineKey = "C";

        //                ConvertPointList(cubicBezierCurveTo, defineKey, stringPath);
        //                break;
        //            }
        //        case DocumentFormat.OpenXml.Drawing.CloseShapePath closeShapePath:
        //            {
        //                var defineKey = "Z";
        //                stringPath.Append(defineKey);
        //                break;
        //            }
        //        case DocumentFormat.OpenXml.Drawing.ArcTo arcTo:
        //            throw new Exception("ArcTo（椭圆）还没实现,请联系管理员");
        //            //break;
        //            //    {
        //            //        Degree swingAngDegree = new Degree(0);
        //            //        var swingAngleString = arcTo.SwingAngle;
        //            //        if (swingAngleString != null)
        //            //        {
        //            //            if (int.TryParse(swingAngleString, out var swingAngle))
        //            //            {
        //            //                swingAngDegree = new Degree(swingAngle);
        //            //            }
        //            //        }

        //            //        Degree startAngleDegree = new Degree(0);
        //            //        var startAngleString = arcTo.StartAngle;
        //            //        if (startAngleString != null)
        //            //        {
        //            //            if (int.TryParse(startAngleString.Value, out var startAngle))
        //            //            {
        //            //                startAngleDegree = new Degree(startAngle);
        //            //            }
        //            //        }

        //            //        var widthRadius = EmuStringToEmu(arcTo.WidthRadius);
        //            //        var heightRadius = EmuStringToEmu(arcTo.HeightRadius);

        //            //        return ArcToToString(stringPath, currentPoint, widthRadius, heightRadius,
        //            //            startAngleDegree, swingAngDegree);
        //            //    }

        //    }

        //    void PointToString(DocumentFormat.OpenXml.Drawing.Point point) => PixelPointToString(point, stringPath);
        //}
        //private static void PixelPointToString(DocumentFormat.OpenXml.Drawing.Point point, StringBuilder stringPath)
        //{
        //    stringPath.Append(" ");
        //    stringPath.Append(point.X.Value);
        //    stringPath.Append(",");
        //    stringPath.Append(point.Y.Value);
        //}
        //private static void ConvertPointList(DocumentFormat.OpenXml.OpenXmlCompositeElement element, string defineKey, StringBuilder stringPath)
        //{
        //    stringPath.Append(" ");
        //    stringPath.Append(defineKey);
        //    foreach (var point in element.Elements<DocumentFormat.OpenXml.Drawing.Point>())
        //    {
        //        PixelPointToString(point, stringPath);
        //    }
        //}
        //#endregion


        /// <summary>
        /// 导入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelFileStream"></param>
        /// <param name="sheetName"></param>
        /// <param name="headerRowIndex"></param>
        /// <param name="isCompatible"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static List<T> Import<T>(Stream excelFileStream, string sheetName, int headerRowIndex = 0, bool isCompatible = true,List<ImportColumn> columns = null ) where T : class, new()
        {
            var result = new List<T>();
            var type = typeof(T);
            if (columns == null || columns.Count ==0)
            {
                columns = type.GetColumnsByType();
            }
           
            IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
            ISheet sheet = null;
            int sheetIndex = -1;
            if (int.TryParse(sheetName, out sheetIndex))
            {
                sheet = workbook.GetSheetAt(sheetIndex);
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            var headRow =  sheet.GetRow(headerRowIndex);
            type.ForMatColumnsByType(columns, headRow);
            var dataRowIndex = headerRowIndex + 1;
            for (int i = dataRowIndex; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);

                if (row != null && row.Cells.Count >0)
                {
                    T data = new T();
                    foreach (var column in columns)
                    {
                        if (column.Index != null && column.Index > -1 && column.Index.Value < row.Cells.Count)
                        {
                            ICell cell = row.GetCell(column.Index.Value);
                            var value = CommonHelper.ChangeType(cell.GetValueString(), column.Property.PropertyType);
                            column.Property.SetValue(data, value);
                        } 
                    }
                    result.Add(data);
                }
            }
            return result;
        
        }

        /// <summary>
        /// 创建工作薄
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <returns></returns>
        private static IWorkbook CreateWorkbook(bool isCompatible)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook();
            }
            else
            {
                return new XSSFWorkbook();
            }
        }

        /// <summary>
        /// 创建工作薄(依据文件流)
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <param name="stream"></param>
        /// <returns></returns>
        private static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook(stream);
            }
            else
            {
                return new XSSFWorkbook(stream);
            }
        }

        /// <summary>
        /// 从工作表中生成DataTable
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="headerRowIndex"></param>
        /// <returns></returns>
        private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex,int val)
        {
            DataTable table = new DataTable();
            IRow headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;
            
            if (val == 2)
            {
                for (int i = headerRow.FirstCellNum ; i < cellCount; i++)
                {
                    try
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).GetValueString() + i);
                        table.Columns.Add(column);
                    }
                    catch
                    {
                        DataColumn column = new DataColumn(i.ToString());
                        table.Columns.Add(column);
                    }
                }
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);

                    if (row != null )
                    {
                        DataRow dataRow = table.NewRow();

                        for (int j = row.FirstCellNum; j < table.Columns.Count; j++)
                        {
                            var cell = row.GetCell(j);
                            if (cell != null)
                            {
                                try
                                {
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        //判断是否日期类型
                                        if (IsCellDateFormatted(cell))
                                        {
                                            dataRow[j] = row.GetCell(j).DateCellValue;
                                        }
                                        else
                                        {
                                            dataRow[j] = row.GetCell(j).ToString().Trim();
                                        }
                                    }
                                    else if (cell.CellType == CellType.Formula)
                                    {
                                        dataRow[j] = row.GetCell(j).NumericCellValue.ToString().Trim();
                                    }
                                    else
                                    {
                                        dataRow[j] = row.GetCell(j).ToString().Trim();
                                    }

                                }
                                catch
                                {
                                    dataRow[j] = "";
                                }

                            }
                        }

                        table.Rows.Add(dataRow);
                    }
                }
            }
            else
            {
                for (int i = headerRow.FirstCellNum ; i < cellCount; i++)
                {
                    try
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).GetValueString() + i);
                        table.Columns.Add(column);
                    }
                    catch
                    {
                        DataColumn column = new DataColumn(i.ToString());
                        table.Columns.Add(column);
                    }
                }
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    
                    if (row != null && row.Cells.Count > 0 && !string.IsNullOrEmpty(row.Cells[0].GetValueString()))
                    {
                        DataRow dataRow = table.NewRow();

                        for (int j = row.FirstCellNum; j < table.Columns.Count; j++)
                        {
                            if (j>=row.Cells.Count)
                            {
                                continue;
                            }
                            var cell = row.GetCell(j);
                            if (cell != null)
                            {
                                try
                                {
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        //判断是否日期类型
                                        if (IsCellDateFormatted(cell))
                                        {
                                            dataRow[j] = row.GetCell(j).DateCellValue;
                                        }
                                        else if (cell.CellType == CellType.Formula)
                                        {
                                            dataRow[j] = row.GetCell(j).NumericCellValue.ToString().Trim();
                                        }
                                        else
                                        {
                                            dataRow[j] = row.GetCell(j).ToString().Trim();
                                        }
                                    }
                                    else
                                    {
                                        dataRow[j] = row.GetCell(j).ToString().Trim();
                                    }

                                }
                                catch
                                {
                                    dataRow[j] = "";
                                }

                            }
                        }

                        table.Rows.Add(dataRow);
                    }
                }
            }
            

            return table;
        }

        /// <summary>
        /// 判断cell 是不是时间格式
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static bool IsCellDateFormatted(ICell cell)
        {
            if (cell == null) return false;
            bool bDate = false;

            double d = cell.NumericCellValue;
            if (DateUtil.IsValidExcelDate(d))
            {
                ICellStyle style = cell.CellStyle;
                if (style == null)
                    return false;
                int i = style.DataFormat;
                String f = style.GetDataFormatString();
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    return true;
                }
                switch (i)
                {
                    case 58:
                        return true;
                    default:
                        break;
                }
            }
            return bDate;
        }


    
    }

    public static class ExcelExtend
    {
        public static string GetValueString(this ICell cell) {
            try
            {
                switch (cell.CellType)
                {

                    case CellType.Numeric:
                        if (cell.IsCellDateFormatted())
                        {
                            return cell.DateCellValue.ToString();
                        }
                        else
                        {
                            return cell.NumericCellValue.ToString();
                        }
                    case CellType.String:
                        return cell.StringCellValue.ToString();
                    case CellType.Formula:

                        return cell.NumericCellValue.ToString().Trim();
                    default:
                        return cell.ToString().Trim();
                }
            }
            catch (Exception)
            {

            }


            return "";
        }
        public static bool IsCellDateFormatted(this ICell cell) {

            if (cell == null) return false;
            bool bDate = false;

            double d = cell.NumericCellValue;
            if (DateUtil.IsValidExcelDate(d))
            {
                ICellStyle style = cell.CellStyle;
                if (style == null)
                    return false;
                int i = style.DataFormat;
                String f = style.GetDataFormatString();
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    return true;
                }
                switch (i)
                {
                    case 58:
                        return true;
                    default:
                        break;
                }
            }
            return bDate;
        }

        /// <summary>
        /// 获取导入列
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static List<ImportColumn> GetColumnsByType(this Type type)
        {


            List<ImportColumn> list = new List<ImportColumn>();
            PropertyInfo[] propers = type.GetProperties();
            foreach (var property in propers)
            {
                var attribute = property.GetCustomAttribute<ColumnAttribute>();
                ImportColumn column = new ImportColumn();
                column.Name = attribute.Name;
                column.Index = attribute.Index;
                column.Property = property;
                column.PropertyName = property.Name;
                list.Add(column);
            }
            return list;
        }
        /// <summary>
        /// 检查导入列
        /// </summary>
        /// <param name="type"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static List<ImportColumn> ForMatColumnsByType(this Type type, List<ImportColumn> list, IRow row)
        {
            foreach (var item in list)
            {
                item.Property = type.GetProperty(item.PropertyName);
                if ((!item.Index.HasValue || item.Index < 0))
                {
                   
                }
            }
            if (list.Exists(d => (!d.Index.HasValue || d.Index < 0)))
            {
                for (int i = 0; i < row.Cells.Count; i++)
                {
                    foreach (var item in list)
                    {
                        if ((!item.Index.HasValue || item.Index < 0)  && row.GetCell(i).GetValueString().Trim() == item.Name)
                        {
                            item.Index = i;
                        }
                    }
                }
            }
            
            
            return list;
        }
    }

    public class ImportColumn
    {
        public string Name { get; set; }
        public string PropertyName { get; set; }
        public int? Index { get; set; }

        public PropertyInfo Property { get; set; }
    }
    /// <summary>
    /// 列属性
    /// </summary>
    public class ColumnAttribute : Attribute {
        
        public string Name { get; set; }

        public int Index { get; set; } = -1;
    }
}
